Re: [SQL] select in update

At 1:41 +0200 on 24/11/98, Engard Ferenc wrote:


> Yet another question: (quite busy today... :)
>
> oeptest=> update shorttest2 set b=(select b from shorttest t1 where
>t1.a=1) where a=2;
> ERROR:  parser: parse error at or near "select"
>
> It means that the subquery in update is not implemented? And if it isn't,
> how can I evade it? Maybe select ... into .., but it's not a good idea in a
> big table... :(((
>
> Any help?

The long-standing syntax for this in PostgreSQL is:

UPDATE shorttest2
SET b=t1.b
FROM shorttest t1
WHERE t1.a=1;

Please, look up the manpages for the command you want to invoke, or \h it.
You may discover syntax options that cover what you need. I have PostgreSQL
6.2.1, and running "\h update" in psql yields:

testing=> \h update
Command: update
Description: update tuples
Syntax:
update <class_name> set <attr1>=<expr1>,...<attrN>=<exprN> [from <from_clause>]
[where <qual>];

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma